package wli.java.db;

import java.sql.*;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import wli.java.*;

public class MyDatabaseUtil {
	
	private Connection connect = null;
	private Statement statement = null;
	private PreparedStatement preparedStatement = null;
	private ResultSet resultSet = null;

	public void saveStage(Stage stage) throws Exception {
		try {
			
			Class.forName("com.mysql.jdbc.Driver");
	
			connect = DriverManager.getConnection("jdbc:mysql://localhost:3306/stages?"
							+ "user=root&password=li0578");
			System.out.println("Connected to the database");
	
			statement = connect.createStatement();
		
			preparedStatement = connect
					.prepareStatement("insert into  STAGES.TARGET values (?, ?, ?, ?, ? , ?, ?,?,?,?,?,?,?,?,?)");
			
			preparedStatement.setInt(1, stage.getTargetKey());
			preparedStatement.setString(2, stage.getTargetDescrip());
			preparedStatement.setString(3, stage.getFindingId());
			preparedStatement.setString(4, stage.getFindingStatus());
			preparedStatement.setString(5, stage.getTool());
			preparedStatement.setString(6, stage.getToolVersion());
			preparedStatement.setBoolean(7, stage.getAuthenticatedFinding());
			preparedStatement.setString(8, stage.getGdValName());
			preparedStatement.setString(9, stage.getGdSeverity());
			preparedStatement.setString(10, "WLI"); //Owner
			preparedStatement.setString(11, null); //Note
			preparedStatement.setString(12, null); //Status
			preparedStatement.setString(13, stage.getAssetId()); //ASSET_ID
			preparedStatement.setDate(14, new java.sql.Date(System.currentTimeMillis())); //Date Created
			preparedStatement.setDate(15, new java.sql.Date(System.currentTimeMillis())); //Date last update
			preparedStatement.executeUpdate();
			
			statement.close();
			connect.close();
			
		} catch (Exception e) {
			throw e;
		} finally {
			close();
		}

	}
	public void readDataBase() throws Exception {
		try {
			// This will load the MySQL driver, each DB has its own driver
			Class.forName("com.mysql.jdbc.Driver");
			// Setup the connection with the DB
			connect = DriverManager.getConnection("jdbc:mysql://localhost:3306/stages?" + "user=root&password=li0578");
			System.out.println("Connected to the database");
			// Statements allow to issue SQL queries to the database
			statement = connect.createStatement();
			resultSet = statement.executeQuery("select * from STAGES.ELEMENT");
			writeResultSet(resultSet);

			connect.close();
			System.out.println("Disconnected from database");

			// Result set get the result of the SQL query
			/*
			 * resultSet = statement
			 * .executeQuery("select * from STAGES.TARGET");
			 * writeResultSet(resultSet);
			 * 
			 * // PreparedStatements can use variables and are more efficient
			 * preparedStatement = connect .prepareStatement(
			 * "insert into  FEEDBACK.COMMENTS values (default, ?, ?, ?, ? , ?, ?)"
			 * ); //
			 * "myuser, webpage, datum, summery, COMMENTS from FEEDBACK.COMMENTS"
			 * ); // Parameters start with 1 preparedStatement.setString(1,
			 * "Test"); preparedStatement.setString(2, "TestEmail");
			 * preparedStatement.setString(3, "TestWebpage");
			 * preparedStatement.setDate(4, new java.sql.Date(2009, 12, 11));
			 * preparedStatement.setString(5, "TestSummary");
			 * preparedStatement.setString(6, "TestComment");
			 * preparedStatement.executeUpdate();
			 * 
			 * preparedStatement = connect .prepareStatement(
			 * "SELECT myuser, webpage, datum, summery, COMMENTS from FEEDBACK.COMMENTS"
			 * ); resultSet = preparedStatement.executeQuery();
			 * writeResultSet(resultSet);
			 * 
			 * // Remove again the insert comment preparedStatement = connect
			 * .prepareStatement
			 * ("delete from FEEDBACK.COMMENTS where myuser= ? ; ");
			 * preparedStatement.setString(1, "Test");
			 * preparedStatement.executeUpdate();
			 * 
			 * resultSet = statement
			 * .executeQuery("select * from FEEDBACK.COMMENTS");
			 * writeMetaData(resultSet);
			 */
		} catch (Exception e) {
			throw e;
		} finally {
			close();
		}

	}

	/*
	 * private void writeMetaData(ResultSet resultSet) throws SQLException { //
	 * Now get some metadata from the database // Result set get the result of
	 * the SQL query
	 * 
	 * System.out.println("The columns in the table are: ");
	 * 
	 * System.out.println("Table: " + resultSet.getMetaData().getTableName(1));
	 * for (int i = 1; i<= resultSet.getMetaData().getColumnCount(); i++){
	 * System.out.println("Column " +i + " "+
	 * resultSet.getMetaData().getColumnName(i)); } }
	 */
	private void writeResultSet(ResultSet rs) throws SQLException {
		// ResultSet is initially before the first data set
		while (rs.next()) {
			// It is possible to get the columns via name
			// also possible to get the columns via the column number
			// which starts at 1
			// e.g. resultSet.getSTring(2);

			// String user = resultSet.getString("myuser");
			// String website = resultSet.getString("webpage");
			// String summery = resultSet.getString("summery");
			// Date date = resultSet.getDate("datum");
			// String comment = resultSet.getString("comments");
			// System.out.println("User: " + user);
			// System.out.println("Website: " + website);
			// System.out.println("Summery: " + summery);
			// System.out.println("Date: " + date);

			System.out.println("AAAAAAAAA: " + rs.getString("ELEMENT_KEY"));
			System.out.println("BBBBBBBBB: " + rs.getString("ELEMENT_DESCRIPT"));
		}
	}

	// You need to close the resultSet
	private void close() {
		try {
			if (resultSet != null) {
				resultSet.close();
			}

			if (statement != null) {
				statement.close();
			}

			if (connect != null) {
				connect.close();
			}
		} catch (Exception e) {

		}
	}
}
